﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

namespace MINERP.DAL.Statistics
{
    public class PerformanceDAL : DALBase
    {
        public List<users> GetAllUser()
        {
            return DataEntites.users.ToList();

        }
        public List<users> GetAllUserByGroupId(int inGroupId)
        {
            return DataEntites.users.Where(p => p.tb_WorkerGroup.GroupId == inGroupId).ToList();

        }
        //public List<user_Tech> GetUserFlow(users inUser)
        //{ 
        //    return inUser.tb_Tech
        //}
        /// <summary>
        /// 获取单数
        /// </summary>
        /// <param name="inUserId"></param>
        /// <param name="inTechId"></param>
        /// <param name="inStartTime"></param>
        /// <param name="inEndTine"></param>
        /// <returns></returns>
        public int GetProductNumByIdAndTech(int inUserId, int inTechId, DateTime inStartTime, DateTime inEndTine)
        {
            //string strSql = "select it.orderInfo.OrderProductId from WorkOrderFlowList as it where it.UserId= " + inUserId + " and it.tb_Tech.TechId= " + inTechId + "  and it.CompletedTime>= cast('" + inStartTime + "' as System.DateTime )and it.CompletedTime<=cast('" + inEndTine+"' as System.DateTime)" ;
            //int rtnCount = DataEntites.CreateQuery<int>(strSql).Distinct().Count();

            //return rtnCount;


            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = db.GetStoredProcCommand("GetPhotoCount");
            db.AddInParameter(dbCommand, "@UserId", DbType.Int32, inUserId);
            db.AddInParameter(dbCommand, "@Techid", DbType.Int32, inTechId);
            db.AddInParameter(dbCommand, "@StartTime", DbType.DateTime, inStartTime);
            db.AddInParameter(dbCommand, "@EndTime", DbType.DateTime, inEndTine);
            return (int)db.ExecuteScalar(dbCommand);

        }

        /// <summary>
        /// 获得产品数，照片数，照片面积
        /// </summary>
        /// <param name="inUserId"></param>
        /// <param name="inTechId"></param>
        /// <param name="inStartTime"></param>
        /// <param name="inEndTine"></param>
        /// <param name="orderCount"></param>
        /// <param name="photoCount"></param>
        /// <param name="photoAreas"></param>
        /// <returns></returns>
        public int GetPhotoCountOrderCountPhotoAreas(int inUserId, int inTechId, DateTime inStartTime, DateTime inEndTine, out int orderCount, out int photoCount, out double photoAreas)
        {
            int result = 1;//成功
            orderCount = 0;
            photoCount = 0;
            photoAreas = 0;

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = db.GetStoredProcCommand("GetPhotoCount");
            db.AddInParameter(dbCommand, "@UserId", DbType.Int32, inUserId);
            db.AddInParameter(dbCommand, "@Techid", DbType.Int32, inTechId);
            db.AddInParameter(dbCommand, "@StartTime", DbType.DateTime, inStartTime);
            db.AddInParameter(dbCommand, "@EndTime", DbType.DateTime, inEndTine);

            try
            {
                using (IDataReader dr = db.ExecuteReader(dbCommand))
                {
                    while (dr.Read())
                    {
                        orderCount = dr.GetInt32(0);
                        photoCount = dr.GetInt32(1);
                        photoAreas = dr.GetDouble(2);
                    }
                }
            }
            catch (Exception)
            {
                result = 0;
            }
            return result;
        }




        /// <summary>
        ///  获取照片数量
        ///  
        /// </summary>
        /// <param name="inUserId"></param>
        /// <param name="inTechId"></param>
        /// <param name="inStartTime"></param>
        /// <param name="inEndTine"></param>
        /// <returns></returns>
        public int GetPhotoNumByIdAndTech(int inUserId, int inTechId, DateTime inStartTime, DateTime inEndTine)
        {
            string strSql = "select value it.orderInfo.OrderProductId  from WorkOrderFlowList as it where it.UserId= " + inUserId + " and it.tb_Tech.TechId= " + inTechId + "  and it.CompletedTime>= cast('" + inStartTime + "' as System.DateTime )and it.CompletedTime<=cast('" + inEndTine + "' as System.DateTime)";
            ObjectQuery<int> orders = (new newSampleYPDBEntities()).CreateQuery<int>(strSql).Distinct();
            //List<int> orders = (new newSampleYPDBEntities()).CreateQuery<int>(strSql).Distinct().ToList();
            int rtnCount = 0;
            //List<int> orders2 = orders.ToList();

            foreach (int orderId in orders)
            {
                orderInfo orderinfo = DataEntites.orderInfo.Where(p => p.OrderProductId == orderId).FirstOrDefault();
                orderinfo.photoInfo.Load();
                rtnCount += orderinfo.photoInfo.ToList().Count();

            }
            return rtnCount;
        }
        /// <summary>
        ///  获取照片面积
        /// </summary>
        /// <param name="inUserId"></param>
        /// <param name="inTechId"></param>
        /// <param name="inStartTime"></param>
        /// <param name="inEndTine"></param>
        /// <returns></returns>
        public double GetPhotoAreasByIdAndTech(int inUserId, int inTechId, DateTime inStartTime, DateTime inEndTine)
        {
            string strSql = "select value it.orderInfo.OrderProductId from WorkOrderFlowList as it where it.UserId= " + inUserId + " and it.tb_Tech.TechId= " + inTechId + "  and it.CompletedTime>= cast('" + inStartTime + "' as System.DateTime )and it.CompletedTime<=cast('" + inEndTine + "' as System.DateTime)";

            ObjectQuery<int> orders = (new newSampleYPDBEntities()).CreateQuery<int>(strSql).Distinct();

            double rtnCount = 0;

            foreach (int orderId in orders)
            {
                orderInfo orderinfo = DataEntites.orderInfo.Where(p => p.OrderProductId == orderId).FirstOrDefault();
                orderinfo.photoInfo.Load();

                List<photoInfo> photos = orderinfo.photoInfo.ToList();
                foreach (photoInfo photo in photos)
                {
                    rtnCount += photo.Width * photo.Height * 2.54 * 2.54 / 10000;
                }

            }
            return rtnCount;
        }
        /// <summary>
        /// 取得所有的组
        /// </summary>
        /// <returns></returns>
        public List<tb_WorkerGroup> GetAllGroup()
        {
            return DataEntites.tb_WorkerGroup.ToList();
        }

        public List<EmployeePerformance> getPerformanceAll(string groupName, DateTime startDate, DateTime endDate)
        {
            List<EmployeePerformance> result = new List<EmployeePerformance>();
            using (newSampleYPDBEntities dataBase = new newSampleYPDBEntities())
            {
                result = dataBase.EmployeePerformance.Where(ep => ep.Departments == groupName && ep.CompleteDate >= startDate && ep.CompleteDate <= endDate).ToList();
            }
            return result;

        }

        public List<EmployeePerformance> getPerformanceAll(string groupName, string userName, DateTime startDate, DateTime endDate)
        {
            List<EmployeePerformance> result = new List<EmployeePerformance>();
            using (newSampleYPDBEntities dataBase = new newSampleYPDBEntities())
            {

                result = dataBase.EmployeePerformance.Where(ep => ep.Departments == groupName && ep.UserName == userName && ep.CompleteDate >= startDate && ep.CompleteDate <= endDate).ToList();
            }
            return result;
        }

        public List<EmployeePerformance> getPerformanceAll(DateTime startDate, DateTime endDate)
        {
            List<EmployeePerformance> result = new List<EmployeePerformance>();
            using (newSampleYPDBEntities dataBase = new newSampleYPDBEntities())
            {
                result = dataBase.EmployeePerformance.Where(ep => ep.CompleteDate >= startDate && ep.CompleteDate <= endDate).ToList();
            }
            return result;
        }

        public double GetOneManOutRerunByDay(string inGroupName, string inName, string inTechName, DateTime inStartTime, DateTime inEndTime)
        {
            string Sql = "select sum(OutReturnPhoto.Height*OutReturnPhoto.Weight*2.54*2.54/10000) as areas   " +
           "from dbo.OutReturnMain,dbo.users,dbo.OutReturnPhoto,dbo.tb_Tech,tb_WorkerGroup     " +
           "where OutReturnMain.OutReturnMainId=OutReturnPhoto.OutReturnMainId                 " +
           "and OutReturnMain.ResponUser=users.userID                                          " +
           "and tb_WorkerGroup.GroupId=dbo.users.GroupId                                       " +
           "and OutReturnMain.TechId=tb_Tech.TechId                                            " +
           "and OutReturnPhoto.IsSelect=1                                                      " +
           "and tb_Tech.TechName='" + inTechName + "' " +
           "and users.UserName='" + inName + "' " +
           "and tb_WorkerGroup.GroupName='" + inGroupName + " '" +
           "and ProductCompleteTime between Convert(datetime,'" + inStartTime + "') and convert(datetime,'" + inEndTime + "')      ";
            DataTable table = ExceSQL(Sql);

            return Convert.ToDouble(table.Rows[0][0] is DBNull ? 0.00 : table.Rows[0][0]);
        }
        public   DataTable GetDeliverStat(List<int> inUserIDList, DateTime inStartDate, DateTime inEndDate)
        {
            string userStr = "";
            foreach (int userid in inUserIDList)
            {
                userStr += "," + userid;
            }
            userStr = userStr.TrimStart(',');
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append(" select deliverid,UserName,takeAwayTime,sUM(totalprice) AS TotalPrice,               ");
            stringBuilder.Append(" COUNT(orderinfo_bk.orderproductid) AS productNum,SUM(orderinfo_bk.area ) AS Area    ");
            stringBuilder.Append(" from DeliveryInfo,shop_bill,orderinfo_bk,Bill_Detail,users                          ");
            stringBuilder.Append(" where DeliveryInfo.BillId=shop_bill.BillId and shop_bill.billid=bill_detail.BillId  ");
            stringBuilder.Append(" and bill_detail.OrderInfoCode=orderinfo_bk.OrderProductCode                         ");
            stringBuilder.Append(" and users.userid=DeliveryInfo.userid                                                ");
            stringBuilder.Append(" and DeliveryInfo.status=1                                                           ");
            stringBuilder.Append(" and users.userid in (" + userStr+")");
            stringBuilder.Append(" and takeAwayTime between '"+inStartDate+"' and '"+inEndDate+"'"  );
            stringBuilder.Append(" GROUP BY deliverid,users.userName,takeAwayTime                                      ");
            DataTable table=ExceSQL(stringBuilder.ToString());
            return table;
        }
    }
}
